package com.stm.bi.report.productTrend;

import com.alibaba.fastjson.JSONObject;
import com.stm.base.dto.vo.DictVO;
import com.stm.base.provider.DictionaryProvider;
import com.stm.bi.core.util.DateUtils;
import com.stm.bi.core.util.SqlUtils;
import com.stm.bi.dto.query.*;
import com.stm.bi.enums.DateType;
import com.stm.bi.enums.ParamType;
import com.stm.bi.model.Chart;
import com.stm.bi.model.DatatablePage;
import com.stm.bi.model.RowData;
import com.stm.bi.model.Title;
import com.stm.framework.core.util.StringUtil;
import com.stm.framework.dbcp.JdbcService;
import com.stm.framework.dto.Response;
import com.stm.saas.api.OrderProblemConfigRemoteService;
import com.stm.saas.api.ProductAssortmentRemoteService;
import com.stm.saas.api.ProductCategoryRemoteService;
import com.stm.saas.api.ProductStyleRemoteService;
import com.stm.saas.constant.DictionaryConstants;
import com.stm.saas.dto.query.ProductAssortmentListQry;
import com.stm.saas.dto.query.ProductCategoryListQry;
import com.stm.saas.dto.query.ProductStylePageQry;
import com.stm.saas.dto.vo.OrderProblemConfigVO;
import com.stm.saas.dto.vo.ProductAssortmentVO;
import com.stm.saas.dto.vo.ProductCategoryVO;
import com.stm.saas.dto.vo.ProductStyleQueryVO;
import jakarta.annotation.Resource;
import jakarta.validation.constraints.NotNull;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

import static com.stm.bi.constant.IndexConstants.*;
import static com.stm.bi.constant.PlanConstants.*;
import static com.stm.bi.constant.ProductTrendConstants.*;
import static com.stm.bi.constant.ReportConstants.STRING_ALL_TOTAL;
import static com.stm.bi.report.productTrend.ProductDataQueryExe.DELAY;
import static com.stm.bi.report.productTrend.ProductDataQueryExe.getProdCodeAndName;

@Component
public class ReturnReportQueryExe {

    @Resource
    private JdbcService jdbcService;
    @Resource
    private OrderProblemConfigRemoteService orderProblemConfigRemoteService;
    @Resource
    private ProductCategoryRemoteService productCategoryRemoteService;
    @Resource
    private ProductAssortmentRemoteService productAssortmentRemoteService;
    @Resource
    private ProductStyleRemoteService productStyleRemoteService;
    @Resource
    private DictionaryProvider dictionaryProvider;

    private static final List<GroupField> GROUP_FIELDS = Arrays.asList(
            new GroupField().setFieldName(QTY_FIELD).setGroupType("sum"),
            new GroupField().setFieldName(TOTAL_QTY_FIELD_NAME).setGroupType("sum")
    );


    /**
     * 查询退货率合计推移表
     */
    public Chart<List<List<Object>>> returnRateChart(ChartReportQry reportQry){

        Chart<List<List<Object>>> chart = new Chart<>();
        List<List<Object>> datas = new ArrayList<>();
        List<List<Object>> total = new ArrayList<>();

        List<String> dateList = DateUtils.getHistoryDateList(reportQry.getStartDate(), reportQry.getEndDate(), reportQry.getDateType().getValue(), DELAY);

        if(dateList.isEmpty()){
            return chart;
        }

        //获取查询类
        Response<DimensionDataGroupQry> qryResp = setDataQry(reportQry.getParams(),
                reportQry.getSalesType(),
                reportQry.getStartDate(),
                reportQry.getEndDate(),
                reportQry.getDateType().getValue(),
                reportQry.getDimensionId(),
                reportQry.getDeptId());
        if (!qryResp.isSuccess()) {
            return chart;
        }

        DimensionDataGroupQry qry = qryResp.getData()
                .setGroupBy(Collections.singletonList(DATE_FIELD));

        String sql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");

        //第一次查询，按日期推移
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        //查询目前订单问题类型
        Response<List<OrderProblemConfigVO>> res = orderProblemConfigRemoteService.selectList();
        if(!res.isSuccess() || res.getData().isEmpty()){
            return chart;
        }

        List<OrderProblemConfigVO> orderProblems = res.getData();

        //过滤掉没有配置明细的问题类型
        orderProblems = orderProblems.stream()
                .filter(p -> !p.getProblemTypeList().isEmpty())
                .toList();

        BigDecimal totalQtySum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        BigDecimal returnQtySum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);

        BigDecimal rangeTotalQtySum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        BigDecimal rangeReturnQtySum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        if(!dateList.get(0).equals(qry.getStartDate())){
            List<String> rangeList = getRangeDateList(reportQry.getDateType().getValue(), qry.getStartDate(), dateList.get(0));

            for(String date : rangeList){
                BigDecimal totalQty = maps.stream()
                        .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                        .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);

                rangeTotalQtySum = rangeTotalQtySum.add(totalQty);

                BigDecimal qty = maps.stream()
                        .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                        .map(m -> (BigDecimal) m.get(QTY_FIELD))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);

                rangeReturnQtySum = rangeReturnQtySum.add(totalQty.subtract(qty));
            }
        }

        for(String date : dateList){
            List<Object> row = new ArrayList<>();
            row.add(DateUtils.getReportDateName(date, reportQry.getDateType().getValue()));

            BigDecimal totalQty = maps.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                    .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);

            BigDecimal qty = maps.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                    .map(m -> (BigDecimal) m.get(QTY_FIELD))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);

            totalQtySum = totalQtySum.add(totalQty);
            returnQtySum = returnQtySum.add(totalQty.subtract(qty));

            rangeTotalQtySum = rangeTotalQtySum.add(totalQty);
            rangeReturnQtySum = rangeReturnQtySum.add(totalQty.subtract(qty));

            if(reportQry.getDimensionId().equals(RETURN_STYLE)){
                row.add(totalQty);
                if(DateType.DAY.equals(reportQry.getDateType())) {
                    row.add(totalQty.subtract(qty));
                }else {
                    BigDecimal returnRate = rangeReturnQtySum.multiply(BigDecimal.valueOf(100)).divide(rangeTotalQtySum.doubleValue() == 0 ? BigDecimal.ONE : rangeTotalQtySum, 2, RoundingMode.HALF_UP);
                    row.add(returnRate);
                }
            }else {
                BigDecimal returnRate = rangeReturnQtySum.multiply(BigDecimal.valueOf(100)).divide(rangeTotalQtySum.doubleValue() == 0 ? BigDecimal.ONE : rangeTotalQtySum, 2, RoundingMode.HALF_UP);
                row.add(returnRate);
            }

            datas.add(row);
        }

        BigDecimal returnRateSum = rangeReturnQtySum.multiply(BigDecimal.valueOf(100)).divide(rangeTotalQtySum.doubleValue() == 0 ? BigDecimal.ONE : rangeTotalQtySum, 2, RoundingMode.HALF_UP);

        //款式维度日度查询时展示退货量
        if(reportQry.getDimensionId().equals(RETURN_STYLE)){
            total.add(Arrays.asList( QTY_INDEX_NAME, totalQtySum, null, 0));
            chart.getItems().add( QTY_INDEX_NAME);
            if(DateType.DAY.equals(reportQry.getDateType()) ){
                chart.getItems().add("退货量");
                total.add(Arrays.asList("退货量", returnQtySum, null, 0));
            }else {
                chart.getItems().add( RETURN_RATE_INDEX_NAME);
                total.add(Arrays.asList( RETURN_RATE_INDEX_NAME, returnRateSum + "%", null, 0));
            }

        } else {
            total.add(Arrays.asList( RETURN_RATE_INDEX_NAME, returnRateSum + "%", null, 0));
            chart.getItems().add( RETURN_RATE_INDEX_NAME);

            qry.setGroupBy(Collections.singletonList(RETURN_REASON_FIELD));

            String reasonSql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
            List<Map> reasonMaps = jdbcService.selectList(reasonSql, new Object[]{});

            for(OrderProblemConfigVO orderProblemConfig: orderProblems){

                List<OrderProblemConfigVO.ProblemType> problemTypeList = orderProblemConfig.getProblemTypeList();
                List<String> problemCodes = problemTypeList.stream()
                        .map(OrderProblemConfigVO.ProblemType::getCode)
                        .collect(Collectors.toList());
                List<Map> reasonMap = reasonMaps.stream()
                        .filter(rm -> problemCodes.contains((String) rm.get(RETURN_REASON_FIELD)))
                        .collect(Collectors.toList());

                BigDecimal reasonTotalQty = reasonMap.stream()
                        .map(rm -> (BigDecimal) rm.get(TOTAL_QTY_FIELD_NAME))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                BigDecimal reasonQty = reasonMap.stream()
                        .map(rm -> (BigDecimal) rm.get(QTY_FIELD))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                BigDecimal returnQty = reasonTotalQty.subtract(reasonQty);
                BigDecimal returnRate = returnQty.multiply(BigDecimal.valueOf(100)).divide(rangeTotalQtySum.doubleValue() == 0 ? BigDecimal.ONE : rangeTotalQtySum, 2, RoundingMode.HALF_UP);

                total.add(Arrays.asList(orderProblemConfig.getProblemCategoryName(), returnRate + "%", null, 1));
            }
        }

        chart.setDatas(datas);
        chart.setExtend(total);

        return chart;
    }

    public Chart<List<List<Object>>> returnRateDetailChart(DetailChartReportQry reportQry){

        Chart<List<List<Object>>> chart = new Chart<>();
        List<List<Object>> datas = new ArrayList<>();
        List<List<Object>> total = new ArrayList<>();

        boolean flag = !reportQry.getDimensionId().equals(RETURN_STYLE) || !DateType.DAY.equals(reportQry.getDateType());
        String returnReason = reportQry.getParams().stream().filter(item -> ParamType.RETURN_REASON.getValue().equals(item.getType())).findFirst().map(ParamQry::getCode).orElse(null);
        List<String> fields = getProdCodeAndName(reportQry.getDimensionId());
        String code = fields.getFirst();
        String name = fields.getLast();

        List<String> dateList = DateUtils.getHistoryDateList(reportQry.getStartDate(), reportQry.getEndDate(), reportQry.getDateType().getValue(), DELAY);

        if(dateList.isEmpty()){
            return chart;
        }

        //获取查询类
        Response<DimensionDataGroupQry> qryResp = setDataQry(reportQry.getParams(),
                reportQry.getSalesType(),
                reportQry.getStartDate(),
                reportQry.getEndDate(),
                reportQry.getDateType().getValue(),
                reportQry.getDimensionId(),
                reportQry.getDeptId());
        if (!qryResp.isSuccess()) {
            return chart;
        }

        DimensionDataGroupQry qry = qryResp.getData()
                .setGroupBy(Arrays.asList(code, name));

        String sql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        //第一次查询出总销量, 退货量
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        //设置需要查询的指标
        String indexField = RETURN_RATE_FIELD_NAME;
        if(!flag){
            if(returnReason != null && !returnReason.isEmpty()){
                if(TOTAL_QTY_INDEX_ID.equals(Long.parseLong(returnReason))){
                    indexField = TOTAL_QTY_FIELD_NAME;
                }else if(RETURN_QTY_INDEX_ID.equals(Long.parseLong(returnReason))){
                    indexField = RETURN_QTY_FIELD_NAME;
                }
            }
        }
        
        //获取查询类
        Response<DimensionDataGroupQry> totalQryResp = setDataQry(new ArrayList<>(),
                reportQry.getSalesType(),
                reportQry.getStartDate(),
                reportQry.getEndDate(),
                reportQry.getDateType().getValue(),
                reportQry.getDimensionId(),
                reportQry.getDeptId());
        
        if(!totalQryResp.isSuccess()){
            return chart;
        }

        DimensionDataGroupQry totalQry = totalQryResp.getData()
                .setGroupBy(Collections.singletonList(code));

        String qtySql = SqlUtils.buildSql(totalQry, "ads_base_product_multi_sales");
        List<Map> qtyMap = jdbcService.selectList(qtySql, new Object[]{});

        //初始化并排序
        maps = calculateReturnRateAndSort(maps, qtyMap, code, indexField);

        if(reportQry.getLimit() < 0){
            Collections.reverse(maps);
        }

        List<Map> data = maps.subList(0, Math.min(Math.abs(reportQry.getLimit()), maps.size()));

        for(Map m: data){
            chart.getItems().add(m.get(name) == null ? "其他" : ( "null".equals(m.get(name)) ? "其他":(String)m.get(name)));
            if(flag){
                total.add(Arrays.asList(m.get(name) == null ? "其他" : ("null".equals(m.get(name)) ? "其他":(String)m.get(name)), m.get( RETURN_RATE_FIELD_NAME) + "%", null, 0));
            }else {
                total.add(Arrays.asList(m.get(name) == null ? "其他" : ("null".equals(m.get(name)) ? "其他":(String)m.get(name)), m.get(indexField) + "", null, 0));
            }
        }

        List<String> businessIds = data.stream()
                .map(m -> (String)m.get(code))
                .collect(Collectors.toList());
        
        qry.setGroupBy(Arrays.asList(code, name, DATE_FIELD));
        qry.getOtherConditions().put(code, businessIds);

        String detailSql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        //查询明细
        List<Map> detailMap = jdbcService.selectList(detailSql, new Object[]{});

        Map<String, Map<String, BigDecimal>> returnMap = new HashMap<>();
        if(!dateList.getFirst().equals(qry.getStartDate())) {
            List<String> rangeList = getRangeDateList(reportQry.getDateType().getValue(), qry.getStartDate(), dateList.get(0));
            for(String businessId: businessIds){
                Map<String, BigDecimal> m = returnMap.computeIfAbsent(businessId, k -> new HashMap<>());
                for (String date: rangeList){
                    Map map = detailMap.stream()
                            .filter(dm -> Objects.equals(dm.get(code), businessId) && Objects.equals(dm.get(DATE_FIELD), date))
                            .findAny().orElse(new HashMap());

                    BigDecimal totalQty = map.get(TOTAL_QTY_FIELD_NAME) == null ? BigDecimal.ZERO : (BigDecimal) map.get(TOTAL_QTY_FIELD_NAME);
                    BigDecimal qty = map.get(QTY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) map.get(QTY_FIELD);

                    BigDecimal totalQtySum = totalQty.add(m.get(TOTAL_QTY_FIELD_NAME) == null ? BigDecimal.ZERO : m.get(TOTAL_QTY_FIELD_NAME));
                    BigDecimal qtySum = qty.add(m.get(QTY_FIELD) == null ? BigDecimal.ZERO : m.get(QTY_FIELD));

                    m.put(TOTAL_QTY_FIELD_NAME, totalQtySum);
                    m.put(QTY_FIELD, qtySum);
                    m.put(RETURN_QTY_FIELD_NAME, totalQtySum.subtract(qtySum));
                }
                returnMap.put(businessId, m);
            }
        }

        totalQry.setGroupBy(Arrays.asList(code, DATE_FIELD));
        totalQry.getOtherConditions().put(code, businessIds);

        String totalQtySql = SqlUtils.buildSql(totalQry, "ads_base_product_multi_sales");
        List<Map> totalQtyMaps = jdbcService.selectList(totalQtySql, new Object[]{});

        Map<String, Map<String,BigDecimal>> totalMaps = new HashMap<>();
        for(String businessId: businessIds){
            List<Map> itemTotalQtyMap = totalQtyMaps.stream()
                    .filter(tq -> Objects.equals(tq.get(code), businessId))
                    .collect(Collectors.toList());
            Map<String, BigDecimal> totalQtyMap = getTotalQtyMap(itemTotalQtyMap, dateList, qry);
            totalMaps.put(businessId, totalQtyMap);
        }

        for(String date: dateList){
            List<Object> row = new ArrayList<>();
            row.add(DateUtils.getReportDateName(date, reportQry.getDateType().getValue()));
            for(String businessId: businessIds){
                Map map = detailMap.stream()
                        .filter(dm -> Objects.equals(dm.get(code), businessId) && Objects.equals(dm.get(DATE_FIELD), date))
                        .findAny()
                        .orElse(new HashMap());
                if(flag){
                    Map<String, BigDecimal> m = returnMap.computeIfAbsent(businessId, k -> new HashMap<>());
                    totalMaps.get(businessId).get(date);
                    BigDecimal returnQtySum = m.get(RETURN_QTY_FIELD_NAME) == null ? BigDecimal.ZERO : m.get(RETURN_QTY_FIELD_NAME);
                    BigDecimal totalQtySum = totalMaps.get(businessId).get(date);

                    BigDecimal totalQty = map.get(TOTAL_QTY_FIELD_NAME) == null ? BigDecimal.ZERO : (BigDecimal) map.get(TOTAL_QTY_FIELD_NAME);
                    BigDecimal qty = map.get(QTY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) map.get(QTY_FIELD);

                    returnQtySum = returnQtySum.add(totalQty.subtract(qty));
                    BigDecimal returnRate = returnQtySum.multiply(BigDecimal.valueOf(100)).divide(totalQtySum.doubleValue() == 0 ? BigDecimal.ONE : totalQtySum, 2, RoundingMode.HALF_UP);

                    row.add(returnRate);

                    m.put(RETURN_QTY_FIELD_NAME, returnQtySum);
//                    if(businessId.equals("SVS")){
                    System.out.println("时间：" + date + ", 名称：" + businessId + ", 总销量：" + totalQtySum + ", 退货数：" + returnQtySum + ", 退货率：" + returnRate);
//                    }

                    returnMap.put(businessId, m);
                }else {
                    Map<String, BigDecimal> m = returnMap.computeIfAbsent(businessId, k -> new HashMap<>());
                    BigDecimal totalQty = map.get(TOTAL_QTY_FIELD_NAME) == null ? BigDecimal.ZERO : (BigDecimal) map.get(TOTAL_QTY_FIELD_NAME);
                    BigDecimal qty = map.get(QTY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) map.get(QTY_FIELD);
                    if(TOTAL_QTY_FIELD_NAME.equals(indexField)){
                        row.add(totalQty);
                    }else {
                        row.add(totalQty.subtract(qty));
                    }
                }
            }

            datas.add(row);
        }

        chart.setDatas(datas);
        chart.setExtend(total);

        return chart;
    }

    public DatatablePage returnRateTable(TableReportQry reportQry){
        DatatablePage table = new DatatablePage();
        List<RowData> treeData = new ArrayList<>();

        boolean flag = !reportQry.getDimensionId().equals(RETURN_STYLE) || !DateType.DAY.equals(reportQry.getDateType());
        List<String> fields = getProdCodeAndName(reportQry.getDimensionId());
        String code = fields.getFirst();
        String name = fields.getLast();

        List<String> dateList = DateUtils.getHistoryDateList(reportQry.getStartDate(), reportQry.getEndDate(), reportQry.getDateType().getValue(), DELAY);

        if(dateList.isEmpty()){
            return table;
        }

        List<Title> title = getTableTitle(reportQry.getDateType().getValue(), dateList, "名称");

        //获取查询类
        Response<DimensionDataGroupQry> qryResp = setDataQry(reportQry.getParams(),
                reportQry.getSalesType(),
                reportQry.getStartDate(),
                reportQry.getEndDate(),
                reportQry.getDateType().getValue(),
                reportQry.getDimensionId(),
                reportQry.getDeptId());
        if (!qryResp.isSuccess()) {
            return table;
        }

        DimensionDataGroupQry qry = qryResp.getData()
                .setGroupBy(Arrays.asList(code, name));

        if(TYPE_DAY.equals(reportQry.getDateType().getValue())){
            qry.setStartDate(reportQry.getStartDate().substring(0,4) + "-01-01");
        }

        String sql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        //第一次查询出总销量, 退货量
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        if(flag){
            maps = calculateReturnRateAndSort(maps, code,  RETURN_RATE_FIELD_NAME);
        }else {
            maps = calculateReturnRateAndSort(maps, code, RETURN_QTY_FIELD_NAME);
        }

        //查询目前订单问题类型
        Response<List<OrderProblemConfigVO>> problemResp = orderProblemConfigRemoteService.selectList();
        if(!problemResp.isSuccess() || problemResp.getData().isEmpty()){
            return table;
        }
        List<OrderProblemConfigVO> problems = problemResp.getData();

        qry.setGroupBy(Arrays.asList(DATE_FIELD, RETURN_REASON_FIELD));

        String totalSql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        //第二次查询按日期和退货理由分组
        List<Map> totalMaps = jdbcService.selectList(totalSql, new Object[]{});

        RowData total = returnProcessTableItem("总计", totalMaps, dateList, problems, reportQry.getDateType().getValue(), qry.getStartDate(), flag);

        List<Map> data;
        if(reportQry.getPageNum() == 1){
            treeData.add(total);
            data = maps.subList(0, Math.min(maps.size(), reportQry.getPageSize() - 1));
        }else {
            data = maps.subList(
                    Math.min(maps.size(), (reportQry.getPageNum() - 1) * reportQry.getPageSize() - 1),
                    Math.min(maps.size(), reportQry.getPageNum() * reportQry.getPageSize() - 1)
            );
        }

        List<String> businessIds = data.stream()
                .map(m -> (String) m.get(code))
                .collect(Collectors.toList());

        List<List<String>> items = data.stream()
                .map(m -> Arrays.asList((String) m.get(code), (String) m.get(name)))
                .toList();

        qry.setGroupBy(Arrays.asList(code, name, DATE_FIELD, RETURN_REASON_FIELD));
        qry.getOtherConditions().put(code, businessIds);

        String detailSql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        //第三次查询，查询明细汇总
        List<Map> detailMaps = jdbcService.selectList(detailSql, new Object[]{});
        for(List<String> item: items){
            List<Map> detailMap = detailMaps.stream()
                    .filter(dm -> Objects.equals(dm.get(code), item.get(0)))
                    .collect(Collectors.toList());

            RowData dimensionTableDataItemVO = returnProcessTableItem(item.get(1), detailMap, dateList, problems, reportQry.getDateType().getValue(), qry.getStartDate(), flag);
            treeData.add(dimensionTableDataItemVO);
        }

        table.setDatas(treeData)
                .setTotal(maps.size() + 1)
                .setCurrent(reportQry.getPageNum())
                .setSize(reportQry.getPageSize())
                .setTitles(title);

        return table;
    }

    private List<Title> getTableTitle(String dateType, List<String> dateList, String name) {
        List<Title> title = new ArrayList<>();
        title.add(new Title().setName(name).setCode("name"));
        dateList.forEach(date ->{
            title.add(new Title().setName(DateUtils.getReportDateName(date, dateType)).setCode(date));
        });
        title.add(new Title().setName("合计").setCode("total"));

        return title;
    }

    public RowData returnProcessTableItem(String name, List<Map> maps, List<String> dateList, List<OrderProblemConfigVO> problems, String dateType, String startDate, boolean flag){
        RowData item = new RowData();
        List<RowData> children = new ArrayList<>();

        item.put("name", name);

        BigDecimal totalQtySum = BigDecimal.ZERO;

        BigDecimal qtySum = BigDecimal.ZERO;

        //计算未包含的部分
        Map<Long, BigDecimal> problemReturnQtyMap = new HashMap<>();
        if(!dateList.get(0).equals(startDate)){
            List<String> rangeDateList = getRangeDateList(dateType, startDate, dateList.get(0));
            for(String date: rangeDateList){
                String finalDate = date;
                List<Map> dateMap = maps.stream()
                        .filter(m -> Objects.equals(m.get(DATE_FIELD), finalDate))
                        .collect(Collectors.toList());
                BigDecimal totalQty = dateMap.stream()
                        .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
                BigDecimal qty = dateMap.stream()
                        .map(m -> (BigDecimal) m.get(QTY_FIELD))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                totalQtySum = totalQtySum.add(totalQty);
                qtySum = qtySum.add(qty);

                for(OrderProblemConfigVO problem: problems){
                    List<String> problemTypeList = problem.getProblemTypeList().stream()
                            .map(OrderProblemConfigVO.ProblemType::getCode)
                            .collect(Collectors.toList());

                    if(problemTypeList.isEmpty()){
                        continue;
                    }

                    BigDecimal problemDateReturnQty = problemReturnQtyMap.computeIfAbsent(problem.getId(), k -> BigDecimal.ZERO);

                    List<Map> problemDateMap = dateMap.stream()
                            .filter(dm -> problemTypeList.contains((String) dm.get(RETURN_REASON_FIELD)))
                            .collect(Collectors.toList());

                    BigDecimal problemTotalQty = problemDateMap.stream()
                            .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                            .filter(Objects::nonNull)
                            .reduce(BigDecimal.ZERO, BigDecimal::add);

                    BigDecimal problemQty = problemDateMap.stream()
                            .map(m -> (BigDecimal) m.get(QTY_FIELD))
                            .filter(Objects::nonNull)
                            .reduce(BigDecimal.ZERO, BigDecimal::add);

                    problemReturnQtyMap.put(problem.getId(), problemDateReturnQty.add(problemTotalQty.subtract(problemQty)));
                }
            }
        }

        Map<String, BigDecimal> totalQtyMap = new HashMap<>();
        for(String date: dateList){
            String d;
            if(TYPE_WEEK.equals(dateType)){
                d = DateUtils.getWeekFirstDay(date);
            }else {
                d = date;
            }

            List<Map> dateMap = maps.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), d))
                    .toList();

            BigDecimal totalQty = dateMap.stream()
                    .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

            BigDecimal qty = dateMap.stream()
                    .map(m -> (BigDecimal) m.get(QTY_FIELD))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

            totalQtySum = totalQtySum.add(totalQty);
            qtySum = qtySum.add(qty);

            totalQtyMap.put(d, totalQtySum);

            BigDecimal returnRate = totalQtySum.subtract(qtySum).multiply(BigDecimal.valueOf(100)).divide(totalQtySum.doubleValue() == 0 ? BigDecimal.ONE : totalQtySum, 2, RoundingMode.HALF_UP);

            if(flag){
                if(returnRate.doubleValue() > 0){
                    item.put(d, returnRate + "%");
                }else {
                    item.put(d, null);
                }
                totalQtyMap.put(d, totalQtySum);
            }else {
                if(totalQtySum.subtract(qtySum).intValue() > 0){
                    item.put(d, totalQtySum.subtract(qtySum).setScale(0, RoundingMode.HALF_UP) + "");
                }else {
                    item.put(d, null);
                }

            }

            if(date.equals(dateList.get(dateList.size() - 1))){
                item.put("total", item.get(d));
            }

//            System.out.println("名称：" + name + "，时间：" + date + "，总销量：" + totalQtySum+ "，退货量：" + totalQtySum.subtract(qtySum) + "，退货率：" + returnRate);
        }

        for(OrderProblemConfigVO problem: problems){
            List<String> problemTypeList = problem.getProblemTypeList().stream()
                    .map(OrderProblemConfigVO.ProblemType::getCode)
                    .collect(Collectors.toList());

            if(problemTypeList.isEmpty()){
                continue;
            }

            RowData child = new RowData();

            child.put("name", problem.getProblemCategoryName());

            BigDecimal problemReturnQty = problemReturnQtyMap.getOrDefault(problem.getId(), BigDecimal.ZERO);
            for(String date: dateList){
                String d;
                if(TYPE_WEEK.equals(dateType)){
                    d = DateUtils.getWeekFirstDay(date);
                }else {
                    d = date;
                }
                BigDecimal totalQty = totalQtyMap.get(d);

                List<Map> problemDateMap = maps.stream()
                        .filter(m -> Objects.equals(m.get(DATE_FIELD), d))
                        .filter(m -> problemTypeList.contains((String) m.get(RETURN_REASON_FIELD)))
                        .collect(Collectors.toList());

                BigDecimal problemTotalQty = problemDateMap.stream()
                        .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                BigDecimal problemQty = problemDateMap.stream()
                        .map(m -> (BigDecimal) m.get(QTY_FIELD))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                BigDecimal returnQty = problemTotalQty.subtract(problemQty);
                problemReturnQty = problemReturnQty.add(returnQty);

                BigDecimal returnRate = problemReturnQty.multiply(BigDecimal.valueOf(100)).divide(totalQty.doubleValue() == 0 ? BigDecimal.ONE : totalQty, 2, RoundingMode.HALF_UP);
                if(flag){
                    if(returnRate.doubleValue() > 0){
                        child.put(d, returnRate + "%");
                    }else {
                        child.put(d, null);
                    }
//                    childMap.put(d, problemReturnQty.multiply(BigDecimal.valueOf(100)).divide(totalQty.doubleValue() == 0 ? BigDecimal.ONE : totalQty, 2, RoundingMode.HALF_UP) + "%");
                }else {
                    if(problemReturnQty.intValue() > 0){
                        child.put(d, problemReturnQty.setScale(0, RoundingMode.HALF_UP) + "");
                    }else {
                        child.put(d, null);
                    }
                }

                if(date.equals(dateList.get(dateList.size() - 1))){
                    child.put("total", child.get(d));
                }
            }
            problemReturnQtyMap.put(problem.getId(), problemReturnQty);
            children.add(child);
        }
        item.setChildren(children);
        return item;
    }



    public Chart<List<List<Object>>> returnReasonChart(ChartReportQry reportQry) {
        Chart<List<List<Object>>> chart = new Chart<>();
        List<List<Object>> datas = new ArrayList<>();
        List<List<Object>> total = new ArrayList<>();

        // 获取参数
        List<String> codes = new ArrayList<>();
        List<ParamQry> params = reportQry.getParams().stream().filter(e -> e.getType().equals(ParamType.RETURN_REASON.getValue())).collect(Collectors.toList());
        if (!params.isEmpty()) {
            String code = params.get(0).getCode();
            if (code.contains(",")) {
                codes = Arrays.stream(code.split(",")).collect(Collectors.toList());
            } else {
                codes.add(code);
            }
        }
        Long dimensionId = reportQry.getDimensionId();
        Map<String, Object> map = new HashMap<>();

        List<GroupField> groupFields = Arrays.asList(
                new GroupField().setFieldName(QTY_FIELD).setGroupType("sum"),
                new GroupField().setFieldName(TOTAL_QTY_FIELD_NAME).setGroupType("sum")
        );

        List<String> dateList = DateUtils.getHistoryDateList(reportQry.getStartDate(), reportQry.getEndDate(), reportQry.getDateType().getValue(), DELAY);

        if(dateList.isEmpty()){
            return chart;
        }

        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setSalesType(reportQry.getSalesType())
                .setDeptId(reportQry.getDeptId())
                .setGroupFields(groupFields)
                .setGroupBy(Arrays.asList(DATE_FIELD, RETURN_REASON_FIELD))
                .setDateType(reportQry.getDateType().getValue())
                .setStartDate(dateList.getFirst())
                .setEndDate(dateList.getLast());

        String sql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});


        Response<List<OrderProblemConfigVO>> res = orderProblemConfigRemoteService.selectList();
        if (!res.isSuccess() || res.getData().isEmpty()) {
            return chart;
        }

        List<OrderProblemConfigVO> orderProblems = res.getData();
        List<DictVO> problemCategoryDicts = dictionaryProvider.selectList(DictionaryConstants.ORDER_PROBLEM_CATEGORY);
        problemCategoryDicts = problemCategoryDicts.stream().filter(e -> !"hidden".equals(e.getRemark())).collect(Collectors.toList());
        List<DictVO> finalProblemCategoryDicts = problemCategoryDicts;
        orderProblems = orderProblems.stream().filter(e -> finalProblemCategoryDicts.stream().anyMatch(p -> e.getProblemCategory().equals(p.getValue()))).collect(Collectors.toList());

        List<String> finalCodes = codes;
        if (finalCodes.isEmpty()){
            orderProblems = orderProblems.stream()
                    .filter(p -> !p.getProblemTypeList().isEmpty())
                    .collect(Collectors.toList());
        }else {
            orderProblems = orderProblems.stream()
                    .filter(e -> finalCodes.stream().anyMatch(c -> c.equals(e.getProblemCategory())))
                    .filter(p -> !p.getProblemTypeList().isEmpty())
                    .collect(Collectors.toList());
        }
        // 总销量
        BigDecimal currentTotalQty = maps.stream()
                .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                .filter(Objects::nonNull)
                .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);
        if (currentTotalQty.doubleValue() == 0) {
            currentTotalQty = currentTotalQty.add(BigDecimal.ONE);
        }

        BigDecimal totalQtySum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        BigDecimal returnQtySum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        // 合计退货量
        BigDecimal totalReturnQtySum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        BigDecimal returnRateSum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);

//        Map<String, BigDecimal> problemQtyMap = new HashMap<>();
        Map<String, BigDecimal> problemReturnQtyMap = new HashMap<>();

        for (String date : dateList) {
            List<Object> row = new ArrayList<>();
            row.add(DateUtils.getReportDateName(date, reportQry.getDateType().getValue()));

            BigDecimal totalQty = maps.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                    .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);

            BigDecimal qty = maps.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                    .map(m -> (BigDecimal) m.get(QTY_FIELD))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);
            totalQtySum = totalQtySum.add(totalQty);
            returnQtySum = returnQtySum.add(totalQty.subtract(qty));

            // 遍历退货理由
            for (OrderProblemConfigVO orderProblemConfig : orderProblems) {

                List<String> problemCodes = orderProblemConfig.getProblemTypeList().stream()
                        .map(OrderProblemConfigVO.ProblemType::getCode)
                        .collect(Collectors.toList());

                List<Map> problemMaps = maps.stream()
                        .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                        .filter(m -> problemCodes.contains((String) m.get(RETURN_REASON_FIELD)))
                        .collect(Collectors.toList());

                BigDecimal tQty = problemMaps.stream()
                        .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);

                BigDecimal problemQty = problemMaps.stream()
                        .map(m -> (BigDecimal) m.get(QTY_FIELD))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP), BigDecimal::add);

                BigDecimal returnQty = tQty.subtract(problemQty);

                problemReturnQtyMap.put(orderProblemConfig.getProblemCategory(),
                        returnQty.add(problemReturnQtyMap.get(orderProblemConfig.getProblemCategory()) == null ?
                                BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP) : problemReturnQtyMap.get(orderProblemConfig.getProblemCategory())));

                // 日度为退货量，月度为退货率
                if (DateType.DAY.equals(reportQry.getDateType())) {
                    row.add(returnQty);
                    totalReturnQtySum = totalReturnQtySum.add(returnQty);
                }else{
                    // 当前退货理由
                    // 当前日期当前退货理由总销量
                    BigDecimal problemReturnQty = problemReturnQtyMap.get(
                            orderProblemConfig.getProblemCategory()) == null ? BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP) : problemReturnQtyMap.get(orderProblemConfig.getProblemCategory()
                    );
                    BigDecimal returnRate = null;
                    if(totalQtySum.doubleValue() != 0){
                        returnRate =  problemReturnQty.multiply(BigDecimal.valueOf(100)).divide(totalQtySum, 2, RoundingMode.HALF_UP);
                    }
                    row.add(returnRate);
                }
            }
            datas.add(row);
        }
        // 合计退货量/率
        if (DateType.DAY.equals(reportQry.getDateType())) {
            // 整体合计
            total.add(Arrays.asList(STRING_ALL_TOTAL, totalReturnQtySum,null,0));
            for (OrderProblemConfigVO orderProblemConfig : orderProblems) {
                chart.getItems().add(orderProblemConfig.getProblemCategoryName());
                BigDecimal returnQty = problemReturnQtyMap.get(
                        orderProblemConfig.getProblemCategory()) == null ? BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP) : problemReturnQtyMap.get(orderProblemConfig.getProblemCategory()
                );
                total.add(Arrays.asList(orderProblemConfig.getProblemCategoryName(), returnQty,null,0));
            }
        }else{

            // 整体合计
            total.add(Arrays.asList(STRING_ALL_TOTAL, null,null,0));
            for (OrderProblemConfigVO orderProblemConfig : orderProblems) {
                chart.getItems().add(orderProblemConfig.getProblemCategoryName());
                BigDecimal returnQty = problemReturnQtyMap.get(
                        orderProblemConfig.getProblemCategory()) == null ? BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP) : problemReturnQtyMap.get(orderProblemConfig.getProblemCategory()
                );
                BigDecimal returnRate = returnQty.multiply(BigDecimal.valueOf(100)).divide(currentTotalQty, 2, RoundingMode.HALF_UP);
                returnRateSum = returnRateSum.add(returnRate);
                total.add(Arrays.asList(orderProblemConfig.getProblemCategoryName(), returnRate + "%",null,0));
            }
            total.set(0,Arrays.asList(STRING_ALL_TOTAL, returnRateSum + "%",null,0));
        }

        chart.setDatas(datas);
        chart.setExtend(total);

        return chart;
    }

    public Chart<List<List<Object>>> returnReasonDetailChart(DetailChartReportQry reportQry) {
        BigDecimal defaultNum = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
        // 获取参数
        List<String> codes = new ArrayList<>();
        List<ParamQry> params = reportQry.getParams().stream().filter(e -> e.getType().equals(ParamType.RETURN_REASON.getValue())).collect(Collectors.toList());
        if (!params.isEmpty()) {
            String code = params.get(0).getCode();
            if (code.contains(",")) {
                codes = Arrays.stream(code.split(",")).collect(Collectors.toList());
            } else {
                codes.add(code);
            }
        }

        Chart<List<List<Object>>> chart = new Chart<>();
        List<List<Object>> datas = new ArrayList<>();
        List<List<Object>> total = new ArrayList<>();

        Long dimensionId = reportQry.getDimensionId();
        Map<String, Object> other = new HashMap<>();

        List<String> problemCodes = new ArrayList<>();
        Response<List<OrderProblemConfigVO>> resonse = orderProblemConfigRemoteService.selectList();
        if(!resonse.isSuccess() || resonse.getData().isEmpty()){
            return chart;
        }

        List<OrderProblemConfigVO> orderProblems = resonse.getData();
        List<DictVO> problemCategoryDicts = dictionaryProvider.selectList(DictionaryConstants.ORDER_PROBLEM_CATEGORY);
        problemCategoryDicts = problemCategoryDicts.stream().filter(e -> !"hidden".equals(e.getRemark())).collect(Collectors.toList());
        List<DictVO> finalProblemCategoryDicts = problemCategoryDicts;
        orderProblems = orderProblems.stream().filter(e -> finalProblemCategoryDicts.stream().anyMatch(p -> e.getProblemCategory().equals(p.getValue()))).collect(Collectors.toList());

        List<String> finalCodes = codes;
        if (finalCodes.isEmpty()){
            orderProblems = orderProblems.stream()
                    .filter(p -> !p.getProblemTypeList().isEmpty())
                    .collect(Collectors.toList());
        }else {
            orderProblems = orderProblems.stream()
                    .filter(e -> finalCodes.stream().anyMatch(c -> c.equals(e.getProblemCategory())))
                    .filter(p -> !p.getProblemTypeList().isEmpty())
                    .collect(Collectors.toList());
        }
        problemCodes = orderProblems.stream()
                .map(OrderProblemConfigVO::getProblemTypeList)
                .flatMap(List::stream) // 扁平化处理，将所有ProblemType List合并为一个流
                .map(OrderProblemConfigVO.ProblemType::getCode) // 映射到code
                .collect(Collectors.toList());

        if(!problemCodes.isEmpty()){
            other.put(RETURN_REASON_FIELD, problemCodes);
        }



        List<GroupField> groupFields = Arrays.asList(
                new GroupField().setFieldName(QTY_FIELD).setGroupType("sum"),
                new GroupField().setFieldName(TOTAL_QTY_FIELD_NAME).setGroupType("sum")
        );

        List<String> dateList = DateUtils.getHistoryDateList(reportQry.getStartDate(), reportQry.getEndDate(), reportQry.getDateType().getValue(), DELAY);

        if(dateList.isEmpty()){
            return chart;
        }

        List<String> groupBy = new ArrayList<>(Arrays.asList(STYLE_CODE_FIELD, STYLE_NAME_FIELD));
        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setSalesType(reportQry.getSalesType())
                .setDeptId(reportQry.getDeptId())
                .setGroupFields(groupFields)
                .setGroupBy(groupBy)
                .setDateType(reportQry.getDateType().getValue())
                .setStartDate(dateList.get(0))
                .setEndDate(dateList.get(dateList.size() - 1))
                .setOtherConditions(other);

        String sql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        DimensionDataGroupQry qtyQry = new DimensionDataGroupQry()
                .setSalesType(reportQry.getSalesType())
                .setDeptId(reportQry.getDeptId())
                .setGroupFields(groupFields)
                .setGroupBy(groupBy)
                .setDateType(reportQry.getDateType().getValue())
                .setStartDate(dateList.get(0))
                .setEndDate(dateList.get(dateList.size() - 1));

        String qtySql = SqlUtils.buildSql(qtyQry, "ads_base_product_multi_sales");
        List<Map> qtyMaps = jdbcService.selectList(qtySql, new Object[]{});

        if (DateType.DAY.equals(reportQry.getDateType())){
            // 日度退货量
            maps = maps.stream()
                    .peek(e -> {
                        BigDecimal totalQty = (BigDecimal) e.get(TOTAL_QTY_FIELD_NAME);
                        BigDecimal qty = (BigDecimal) e.get(QTY_FIELD);
                        BigDecimal returnQty = totalQty.subtract(qty);
                        e.put("returnQty", returnQty);
                    })
                    .collect(Collectors.toList());
        }else{
            // 月度退货率
            maps = maps.stream()
                    .peek(e -> {
                        // 每个款式的总量
                        BigDecimal styleTotalQty = qtyMaps.stream()
                                .filter(m -> m.get(STYLE_CODE_FIELD).equals(e.get(STYLE_CODE_FIELD)))
                                .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                                .filter(Objects::nonNull)
                                .reduce(defaultNum, BigDecimal::add);
                        if (styleTotalQty.doubleValue() == 0) {
                            styleTotalQty = styleTotalQty.add(BigDecimal.ONE);
                        }
                        BigDecimal totalQty = (BigDecimal) e.get(TOTAL_QTY_FIELD_NAME);
                        BigDecimal qty = (BigDecimal) e.get(QTY_FIELD);
                        BigDecimal returnQty = totalQty.subtract(qty);
                        BigDecimal returnRate = returnQty.multiply(BigDecimal.valueOf(100)).divide(styleTotalQty, 2, RoundingMode.HALF_UP);
                        e.put("returnRate", returnRate);
                    })
                    .collect(Collectors.toList());
        }
        // 区分正序倒序排序
        if (reportQry.getLimit() > 0) {
            if (DateType.DAY.equals(reportQry.getDateType())){
                maps.sort(Comparator.comparing(e -> (BigDecimal) e.get("returnQty"), Comparator.reverseOrder()));
            }else{
                maps.sort(Comparator.comparing(e -> (BigDecimal) e.get("returnRate"), Comparator.reverseOrder()));
            }
        } else {
            if (DateType.DAY.equals(reportQry.getDateType())){
                maps.sort(Comparator.comparing(e -> (BigDecimal) e.get("returnQty")));
            }else{
                maps.sort(Comparator.comparing(e -> (BigDecimal) e.get("returnRate")));

            }
        }
        // 截取
        maps = maps.subList(0, Math.min(maps.size(), Math.abs(reportQry.getLimit())));

        List<String> businessIds = maps.stream()
                .map(m -> (String) m.get(STYLE_CODE_FIELD))
                .filter(Objects::nonNull)
                .collect(Collectors.toList());

        groupBy.add(DATE_FIELD);
        qry.setGroupBy(groupBy);
        qry.getOtherConditions().put(STYLE_CODE_FIELD, businessIds);
        String resQtySql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        List<Map> resQtyMap = jdbcService.selectList(resQtySql, new Object[]{});

        qry.setOtherConditions(null);
        String resSql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        List<Map> resMap = jdbcService.selectList(resSql, new Object[]{});
        Map<String,BigDecimal> totalMap = new HashMap<>();
        Map<String, BigDecimal> totalReturnQtyMap = new HashMap<>();
        Map<String, BigDecimal> styleTotalQtyMap = new HashMap<>();
        for(String date : dateList){
            List<Object> row = new ArrayList<>();
            row.add(DateUtils.getReportDateName(date, reportQry.getDateType().getValue()));

            for(String businessId: businessIds){
                Map res = resMap.stream()
                        .filter(m -> m.get(STYLE_CODE_FIELD).equals(businessId) && m.get(DATE_FIELD).equals(date))
                        .findAny().orElse(new HashMap());
                Map qtyRes = resQtyMap.stream()
                        .filter(m -> m.get(STYLE_CODE_FIELD).equals(businessId) && m.get(DATE_FIELD).equals(date))
                        .findAny().orElse(new HashMap());
                if (DateType.DAY.equals(reportQry.getDateType())) {
                    // 日度退货量
                    BigDecimal totalQty = (BigDecimal) qtyRes.get(TOTAL_QTY_FIELD_NAME) == null ? defaultNum : (BigDecimal) qtyRes.get(TOTAL_QTY_FIELD_NAME);
                    BigDecimal qty = (BigDecimal) qtyRes.get(QTY_FIELD) == null ? defaultNum : (BigDecimal) qtyRes.get(QTY_FIELD);
                    BigDecimal returnQty = totalQty.subtract(qty);
                    row.add(returnQty == null ? defaultNum : returnQty);
                    if (returnQty != null) {
                        returnQty = returnQty.add(totalMap.get(businessId) == null ? defaultNum: totalMap.get(businessId));
                        totalMap.put(businessId, returnQty);
                    }
                } else {
                    // 月度退货率
                    BigDecimal totalQty = (BigDecimal) res.get(TOTAL_QTY_FIELD_NAME) == null ? defaultNum : (BigDecimal) res.get(TOTAL_QTY_FIELD_NAME);
                    BigDecimal qtyTotalQty = (BigDecimal) qtyRes.get(TOTAL_QTY_FIELD_NAME) == null ? defaultNum : (BigDecimal) qtyRes.get(TOTAL_QTY_FIELD_NAME);
                    BigDecimal qty = (BigDecimal) qtyRes.get(QTY_FIELD) == null ? defaultNum : (BigDecimal) qtyRes.get(QTY_FIELD);
                    styleTotalQtyMap.put(businessId, totalQty.add(styleTotalQtyMap.get(businessId) == null ?
                            BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP) : styleTotalQtyMap.get(businessId)));
                    BigDecimal returnQty = qtyTotalQty.subtract(qty);
                    totalReturnQtyMap.put(businessId,
                            returnQty.add(totalReturnQtyMap.get(businessId) == null ?
                                    BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP) : totalReturnQtyMap.get(businessId)));
                    BigDecimal totalReturnQty = totalReturnQtyMap.get(businessId) == null ? BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP) : totalReturnQtyMap.get(businessId);
                    BigDecimal styleTotalQty = styleTotalQtyMap.get(businessId) == null ? BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP) : styleTotalQtyMap.get(businessId);
                    if (styleTotalQty.compareTo(BigDecimal.ZERO) == 0){
                        styleTotalQty = styleTotalQty.add(BigDecimal.ONE);
                    }
                    BigDecimal returnRate = totalReturnQty.multiply(BigDecimal.valueOf(100)).divide(styleTotalQty, 2, RoundingMode.HALF_UP);
                    if (returnRate != null) {
                        totalMap.put(businessId, returnRate);
                    }
                    row.add(totalMap.get(businessId) == null ? defaultNum : totalMap.get(businessId));
                    if (businessId.equals("233XLLS0426")) {
                        System.out.println("月份:" +date +"除数：" + styleTotalQty + "  被除数：" + totalReturnQty + "  商：" + returnRate);
                    }
                }

            }
            datas.add(row);
        }
        // 设置累计数据
        for(String businessId: businessIds){
            Map res = resMap.stream()
                    .filter(m -> m.get(STYLE_CODE_FIELD).equals(businessId) )
                    .findAny().orElse(new HashMap());
            chart.getItems().add(res.get(STYLE_NAME_FIELD).toString());
            if(DateType.DAY.equals(reportQry.getDateType())){
                total.add(Arrays.asList(res.get(STYLE_NAME_FIELD).toString(), totalMap.get(businessId),null, 0));
            }else{
                total.add(Arrays.asList(res.get(STYLE_NAME_FIELD).toString(), totalMap.get(businessId) + "%",null,0));
            }
        }

        chart.setDatas(datas);
        chart.setExtend(total);

        return chart;

    }

    public DatatablePage returnReasonTable(TableReportQry reportQry){
        DatatablePage table = new DatatablePage();
        List<RowData> treeData = new ArrayList<>();
        String returnReason = reportQry.getParams().stream().filter(item -> ParamType.RETURN_REASON.getValue().equals(item.getType())).findFirst().map(ParamQry::getCode).orElse(null);

        boolean flag = !DateType.DAY.equals(reportQry.getDateType());

        List<String> dateList = DateUtils.getHistoryDateList(reportQry.getStartDate(), reportQry.getEndDate(), reportQry.getDateType().getValue(), DELAY);

        if(dateList.isEmpty()){
            return table;
        }

        List<Title> title = getTableTitle(reportQry.getDateType().getValue(), dateList, "退货理由");

        //查询目前订单问题类型
        Response<List<OrderProblemConfigVO>> problemResp = orderProblemConfigRemoteService.selectList();
        if(!problemResp.isSuccess() || problemResp.getData().isEmpty()){
            return table;
        }
        List<OrderProblemConfigVO> problems = problemResp.getData();

        List<String> searchCodes;
        if(StringUtil.isNotEmpty(returnReason)){
            searchCodes = Arrays.asList(returnReason.split(","));
        }else {
            searchCodes = new ArrayList<>();
        }
        problems = problems.stream()
                .filter(p -> !Objects.equals("hidden", p.getRemark()))
                .filter(p -> {
                    if(searchCodes.isEmpty()){
                        return true;
                    }else {
                        return searchCodes.contains(p.getProblemCategory());
                    }
                })
                .collect(Collectors.toList());

        List<String> problemDetails = problems.stream()
                .map(OrderProblemConfigVO::getProblemTypeList)
                .flatMap(Collection::stream)
                .map(OrderProblemConfigVO.ProblemType::getCode)
                .collect(Collectors.toList());

        Response<DimensionDataGroupQry> qryResp = setDataQry(reportQry.getParams(),
                reportQry.getSalesType(),
                reportQry.getStartDate(),
                reportQry.getEndDate(),
                reportQry.getDateType().getValue(),
                reportQry.getDimensionId(),
                reportQry.getDeptId(),
                problemDetails);
        if (!qryResp.isSuccess()) {
            return table;
        }

        DimensionDataGroupQry qry = qryResp.getData()
                .setGroupBy(List.of(RETURN_REASON_FIELD));

        String sql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

//        qry.setOtherConditions(null)
//                .setGroupBy(null);
//        String qtySql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
//        List<Map> qtyMaps = jdbcService.selectList(qtySql, new Object[]{});

        maps = calculateAccumulatedReturnQtyMap(maps, problems, new ArrayList<>(), reportQry.getDateType().getValue());

        if(flag){
            maps = calculateReturnRateAndSort(maps, STYLE_CODE_FIELD, RETURN_RATE_FIELD_NAME);
        }else {
            maps = calculateReturnRateAndSort(maps, STYLE_CODE_FIELD, RETURN_QTY_FIELD_NAME);
        }

        qry.setGroupBy(Collections.singletonList(DATE_FIELD));

        String totalSql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        List<Map> totalMap = jdbcService.selectList(totalSql, new Object[]{});

        Response<DimensionDataGroupQry> totalQryResp = setDataQry(new ArrayList<>(),
                reportQry.getSalesType(),
                reportQry.getStartDate(),
                reportQry.getEndDate(),
                reportQry.getDateType().getValue(),
                reportQry.getDimensionId(),
                reportQry.getDeptId());
        if(!totalQryResp.isSuccess()){
            return table;
        }

        DimensionDataGroupQry totalQry = totalQryResp.getData()
                .setGroupBy(Collections.singletonList(DATE_FIELD));

        String totalQtySql = SqlUtils.buildSql(totalQry, "ads_base_product_multi_sales");
        List<Map> totalQtyMaps = jdbcService.selectList(totalQtySql, new Object[]{});

        Map<String, BigDecimal> totalQtyMap = getTotalQtyMap(totalQtyMaps, dateList, qry);

        totalMap = calculateAccumulatedReturnQtyMap(totalMap, new ArrayList<>(), dateList, reportQry.getDateType().getValue());

        RowData total = returnReasonProcessTableItem("合计", totalMap, totalQtyMap, reportQry.getDateType().getValue(), dateList, qry.getStartDate(), flag);


        List<Map> data;
        if(reportQry.getPageNum() == 1){
            treeData.add(total);
            data = maps.subList(0, Math.min(maps.size(), reportQry.getPageSize() - 1));
        }else {
            data = maps.subList(
                    Math.min(maps.size(), (reportQry.getPageNum() - 1) * reportQry.getPageSize() - 1),
                    Math.min(maps.size(), reportQry.getPageNum() * reportQry.getPageSize() - 1)
            );
        }

        List<Long> codes = data.stream()
                .map(d -> (Long) d.get(RETURN_REASON_FIELD))
                .toList();

        qry.setGroupBy(Arrays.asList(DATE_FIELD, RETURN_REASON_FIELD));

        String detailSql = SqlUtils.buildSql(qry, "ads_base_product_multi_sales");
        List<Map> detailMap = jdbcService.selectList(detailSql, new Object[]{});

        detailMap = calculateAccumulatedReturnQtyMap(detailMap, problems, dateList, reportQry.getDateType().getValue());

        for(Long code: codes){

            OrderProblemConfigVO problem = problems.stream()
                    .filter(p -> Objects.equals(p.getId(), code))
                    .findAny()
                    .orElse(new OrderProblemConfigVO());

//            List<Long> problemCode = problems.stream()
//                    .map(OrderProblemConfigVO::getId)
//                    .collect(Collectors.toList());

            List<Map> codeMaps = detailMap.stream()
                    .filter(d -> Objects.equals(code, d.get(RETURN_REASON_FIELD)))
                    .collect(Collectors.toList());

            RowData item = returnReasonProcessTableItem(problem.getProblemCategoryName(), codeMaps, totalQtyMap, reportQry.getDateType().getValue(), dateList, qry.getStartDate(), flag);
            treeData.add(item);
        }

        table.setDatas(treeData)
                .setTotal(maps.size() + 1)
                .setCurrent(reportQry.getPageNum())
                .setSize(reportQry.getPageSize())
                .setTitles(title);

        return table;
    }

    public RowData returnReasonProcessTableItem(String name, List<Map> maps, Map<String, BigDecimal> totalQtyMap, String dateType, List<String> dateList, String startDate, boolean flag){
        RowData item = new RowData();
        RowData child = new RowData();
        List<RowData> children = new ArrayList<>();

        item.put("name", name);
        if(flag){
            child.put("name", "累计退货量");
        }else {
            child.put("name", "退货率");
        }

        for(String date: dateList){
            String d;
            if(TYPE_WEEK.equals(dateType)){
                d = DateUtils.getWeekFirstDay(date);
            }else {
                d = date;
            }
            List<Map> dateMap = maps.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), d))
                    .toList();

            BigDecimal totalQty = dateMap.stream()
                    .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

//            totalQtySum = totalQtySum.add(totalQty);

            BigDecimal qty = dateMap.stream()
                    .map(m -> (BigDecimal) m.get(QTY_FIELD))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

//            qtySum = qtySum.add(qty);

            BigDecimal returnQty = totalQty.subtract(qty);

//            returnQtySum = returnQtySum.add(totalQty.subtract(qty));

            BigDecimal allTotalQty = totalQtyMap.get(d);
            BigDecimal returnRate = returnQty.multiply(BigDecimal.valueOf(100)).divide(allTotalQty.doubleValue() == 0 ? BigDecimal.ONE : allTotalQty, 2, RoundingMode.HALF_UP);

//            System.out.println("时间[" + date + "]理由[" + name + "]累计退货量[" + returnQtySum + "]总销量[" + allTotalQty + "]退货率[" + returnRate + "%]");
            if(flag){
                if(returnRate.doubleValue() > 0){
                    item.put(d, returnRate + "%");
                }else {
                    item.put(d, null);
                }

                if(returnQty.doubleValue() > 0){
                    child.put(d, returnQty.setScale(0, RoundingMode.HALF_UP) + "");
                }else {
                    child.put(d, null);
                }

            }else {
                if(returnQty.doubleValue() > 0){
                    item.put(d, returnQty.setScale(0, RoundingMode.HALF_UP) + "");
                }else {
                    item.put(d, null);
                }

                if(returnRate.doubleValue() > 0){
                    child.put(d, returnRate + "%");
                }else {
                    child.put(d, null);
                }
            }

            if(date.equals(dateList.get(dateList.size() - 1))){
                item.put("total", item.get(d));
                child.put("total", child.get(d));
            }
        }

        children.add(child);
        item.setChildren(children);
        return item;
    }

    private Map<String, BigDecimal> getTotalQtyMap(List<Map> totalMap, List<String> dateList, DimensionDataGroupQry qry) {

        BigDecimal totalQtySum = BigDecimal.ZERO;
        Map<String, BigDecimal> totalQtyMap = new HashMap<>();
        if(!dateList.getFirst().equals(qry.getStartDate())) {
            List<String> rangeDateList = getRangeDateList(qry.getDateType(), qry.getStartDate(), dateList.get(0));
            for (String date : rangeDateList) {
                BigDecimal totalQty = totalMap.stream()
                        .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                        .map(m -> (BigDecimal)m.get(TOTAL_QTY_FIELD_NAME))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                totalQtySum = totalQtySum.add(totalQty);
            }
        }

        for(String date: dateList){
            BigDecimal totalQty = totalMap.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                    .map(m -> (BigDecimal)m.get(TOTAL_QTY_FIELD_NAME))
                    .filter(Objects::nonNull)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

            totalQtySum = totalQtySum.add(totalQty);

            totalQtyMap.put(date, totalQtySum);
        }

        return totalQtyMap;
    }

    private List<Map> calculateAccumulatedReturnQtyMap(List<Map> maps, List<OrderProblemConfigVO> problems, List<String> dateList, String dateType) {
        List<Map> res = new ArrayList<>();

        if(problems != null && !problems.isEmpty()){
            for(OrderProblemConfigVO problem: problems){
                List<String> problemTypeList = problem.getProblemTypeList().stream()
                        .map(OrderProblemConfigVO.ProblemType::getCode)
                        .collect(Collectors.toList());

                if(problemTypeList.isEmpty()){
                    continue;
                }
                if(dateList == null || dateList.isEmpty()){
                    Map returnReasonMapItem = new HashMap<>();

                    List<Map> problemMap = maps.stream()
                            .filter(m -> problemTypeList.contains((String) m.get(RETURN_REASON_FIELD)))
                            .collect(Collectors.toList());

                    BigDecimal totalQty = problemMap.stream()
                            .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                            .filter(Objects::nonNull)
                            .reduce(BigDecimal.ZERO, BigDecimal::add);

                    BigDecimal qty = problemMap.stream()
                            .map(m -> (BigDecimal) m.get(QTY_FIELD))
                            .filter(Objects::nonNull)
                            .reduce(BigDecimal.ZERO, BigDecimal::add);

                    returnReasonMapItem.put(TOTAL_QTY_FIELD_NAME, totalQty);
                    returnReasonMapItem.put(QTY_FIELD, qty);
                    returnReasonMapItem.put(RETURN_QTY_FIELD_NAME, totalQty.subtract(qty));
                    returnReasonMapItem.put(RETURN_REASON_FIELD, problem.getId());
                    res.add(returnReasonMapItem);
                }else {
                    for (String date : dateList){
                        Map returnReasonMapItem = new HashMap<>();
                        String d;
                        if(TYPE_WEEK.equals(dateType)){
                            d = DateUtils.getWeekFirstDay(date);
                        }else {
                            d = date;
                        }

                        List<Map> problemMap = maps.stream()
                                .filter(m -> problemTypeList.contains((String) m.get(RETURN_REASON_FIELD)))
                                .filter(m -> d.compareTo((String)m.get(DATE_FIELD)) >= 0)
                                .collect(Collectors.toList());

                        BigDecimal totalQty = problemMap.stream()
                                .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                                .filter(Objects::nonNull)
                                .reduce(BigDecimal.ZERO, BigDecimal::add);

                        BigDecimal qty = problemMap.stream()
                                .map(m -> (BigDecimal) m.get(QTY_FIELD))
                                .filter(Objects::nonNull)
                                .reduce(BigDecimal.ZERO, BigDecimal::add);

                        returnReasonMapItem.put(DATE_FIELD, d);
                        returnReasonMapItem.put(TOTAL_QTY_FIELD_NAME, totalQty);
                        returnReasonMapItem.put(QTY_FIELD, qty);
                        returnReasonMapItem.put(RETURN_QTY_FIELD_NAME, totalQty.subtract(qty));
                        returnReasonMapItem.put(RETURN_REASON_FIELD, problem.getId());
                        res.add(returnReasonMapItem);
                    }
                }
            }
        }else {
            for (String date : dateList){
                Map item = new HashMap<>();
                String d;
                if(TYPE_WEEK.equals(dateType)){
                    d = DateUtils.getWeekFirstDay(date);
                }else {
                    d = date;
                }

                List<Map> problemMap = maps.stream()
                        .filter(m -> d.compareTo((String)m.get(DATE_FIELD)) >= 0)
                        .collect(Collectors.toList());

                BigDecimal totalQty = problemMap.stream()
                        .map(m -> (BigDecimal) m.get(TOTAL_QTY_FIELD_NAME))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                BigDecimal qty = problemMap.stream()
                        .map(m -> (BigDecimal) m.get(QTY_FIELD))
                        .filter(Objects::nonNull)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                item.put(DATE_FIELD, d);
                item.put(TOTAL_QTY_FIELD_NAME, totalQty);
                item.put(QTY_FIELD, qty);
                item.put(RETURN_QTY_FIELD_NAME, totalQty.subtract(qty));
                res.add(item);
            }
        }

        return res;
    }


    @NotNull
    private static List<String> getRangeDateList(String dateType, String startDate, String endDate) {
        List<String> rangeList = new ArrayList<>();
        if(TYPE_DAY.equals(dateType)){
            rangeList = DateUtils.getDateList(startDate, DateUtils.getLastDay(endDate), dateType);
        }else if(TYPE_WEEK.equals(dateType)){
            List<String> weekList = DateUtils.getDateList(DateUtils.getWeekFirstDay(startDate),
                    DateUtils.getWeekFirstDay(DateUtils.getLastWeek(endDate)),
                    dateType);

            rangeList = weekList.stream()
                    .map(DateUtils::getWeekFirstDay)
                    .collect(Collectors.toList());
        }
        return rangeList;
    }

    private List<Map> calculateReturnRateAndSort(List<Map> maps, String code, String fieldName) {
        return calculateReturnRateAndSort(maps, new ArrayList<>(), code, fieldName);
    }

    private List<Map> calculateReturnRateAndSort(List<Map> maps, List<Map> totalMaps, String code, String fieldName) {

        if(totalMaps == null || totalMaps.isEmpty()){
            maps = maps.stream()
                    .peek(m -> {
                        BigDecimal totalQty = m.get(TOTAL_QTY_FIELD_NAME) == null ? BigDecimal.ZERO :(BigDecimal) m.get(TOTAL_QTY_FIELD_NAME);
                        BigDecimal qty = (BigDecimal) m.get(QTY_FIELD);
                        BigDecimal returnQty = totalQty.subtract(qty);
                        m.put(RETURN_QTY_FIELD_NAME, returnQty);
                        m.put(RETURN_RATE_FIELD_NAME, returnQty.multiply(BigDecimal.valueOf(100)).divide(totalQty.doubleValue() == 0 ? BigDecimal.ONE : totalQty, 2, RoundingMode.HALF_UP));
                    })
                    .collect(Collectors.toList());
        }else {
            maps = maps.stream()
                    .peek(m -> {
                        BigDecimal allTotalQty = totalMaps.stream()
                                .filter(t -> m.get(code).equals(t.get(code)))
                                .map(t -> (BigDecimal) t.get(TOTAL_QTY_FIELD_NAME))
                                .filter(Objects::nonNull)
                                .reduce(BigDecimal.ZERO, BigDecimal::add);
                        BigDecimal totalQty = m.get(TOTAL_QTY_FIELD_NAME) == null ? BigDecimal.ZERO :(BigDecimal) m.get(TOTAL_QTY_FIELD_NAME);
                        BigDecimal qty = (BigDecimal) m.get(QTY_FIELD);
                        BigDecimal returnQty = totalQty.subtract(qty);
                        BigDecimal returnRate = returnQty.multiply(BigDecimal.valueOf(100)).divide(allTotalQty.doubleValue() == 0 ? BigDecimal.ONE : allTotalQty, 2, RoundingMode.HALF_UP);
//                        if(m.get(BUSINESS_ID_FIELD_NAME).equals("SVS")){
//                            System.out.println("名称：" + m.get(BUSINESS_ID_FIELD_NAME) + ", 总销量：" + allTotalQty + ", 退货数：" + returnQty + ", 退货率：" + returnRate);
//                        }
                        m.put(RETURN_QTY_FIELD_NAME, returnQty);
                        m.put(RETURN_RATE_FIELD_NAME, returnRate);
                    })
                    .collect(Collectors.toList());
        }

        maps.sort(Comparator.comparing((Map m) -> (BigDecimal) m.get(fieldName)).reversed());

        return maps;
    }

    private Response<DimensionDataGroupQry> setDataQry(List<ParamQry> params,
                                                       String salesType,
                                                       String startDate,
                                                       String endDate,
                                                       String dateType,
                                                       Long dimensionId,
                                                       Long deptId) {
        return setDataQry(params, salesType, startDate, endDate, dateType, dimensionId, deptId, null);
    }

    @NotNull
    private Response<DimensionDataGroupQry> setDataQry(List<ParamQry> params,
                                                       String salesType,
                                                       String startDate,
                                                       String endDate,
                                                       String dateType,
                                                       Long dimensionId,
                                                       Long deptId,
                                                       List<String> problemCodes) {
        String prodType = params.stream().filter(item -> ParamType.OUT_SEASON.getValue().equals(item.getType())).findFirst().map(ParamQry::getCode).orElse(null);
        String prodSeason = params.stream().filter(item -> ParamType.SEASON.getValue().equals(item.getType())).findFirst().map(ParamQry::getCode).orElse(null);
        String prodCode = params.stream().filter(item -> ParamType.CODE.getValue().equals(item.getType())).findFirst().map(ParamQry::getCode).orElse(null);
        String returnReason = params.stream().filter(item -> ParamType.RETURN_REASON.getValue().equals(item.getType())).findFirst().map(ParamQry::getCode).orElse(null);
        String prodLevel = params.stream().filter(item -> ParamType.LEVEL.getValue().equals(item.getType())).findFirst().map(ParamQry::getCode).orElse(null);
        String isMainly = params.stream().filter(item -> ParamType.IS_MAINLY.getValue().equals(item.getType())).findFirst().map(ParamQry::getCode).orElse(null);

        String year = startDate.substring(0, 4);

//        String nowDate = LocalDate.now().minusDays(DELAY).toString();

        if(TYPE_MONTH.equals(dateType)){
            startDate = year + "-01";
            endDate = year + "-12";
        }else if(TYPE_WEEK.equals(dateType)){
            startDate = year + "-01-01";
            endDate = year + "-12-31";
        }else if(TYPE_DAY.equals(dateType)){
            if(!dimensionId.equals(RETURN_STYLE)){
                startDate = year + "-01-01";
                endDate = endDate + "-" + DateUtils.getMonthDays(endDate);
            }else {
                if(startDate.length() == 7){
                    startDate = startDate + "-01";
                    endDate = endDate + "-" + DateUtils.getMonthDays(endDate);
                }
            }
        }

        //退货率需要累计计算，所以从年初开始查
        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setSalesType(salesType)
                .setDeptId(deptId)
                .setGroupFields(GROUP_FIELDS)
                .setDateType(dateType)
                .setStartDate(startDate)
                .setEndDate(endDate);


        // 过滤过应季
        Map<String, Object> map = new HashMap<>();
        if (prodType != null && !prodType.isEmpty()) {
            map.put(PROD_SEASON_FIELD, prodType);
        }

        //过滤退货原因
        if((returnReason != null && !returnReason.isEmpty() && !(DateType.DAY.getValue().equals(dateType) && dimensionId.equals(RETURN_STYLE)))
                || (problemCodes != null && !problemCodes.isEmpty())
        ) {
            List<String> codes = new ArrayList<>();
            if(problemCodes != null && !problemCodes.isEmpty()){
                codes = problemCodes;
            }

            if(returnReason != null && !returnReason.isEmpty() && !(DateType.DAY.getValue().equals(dateType) && dimensionId.equals(RETURN_STYLE))){
                Response<List<OrderProblemConfigVO>> res = orderProblemConfigRemoteService.selectList();
                if(!res.isSuccess() || res.getData().isEmpty()){
                    return Response.failure("获取订单问题列表数据失败");
                }

                List<OrderProblemConfigVO> orderProblems = res.getData();

                List<String> reasons = Arrays.asList(returnReason.split(","));

                List<String> searchCodes = orderProblems.stream()
                        .filter(p -> reasons.contains(p.getProblemCategory()))
                        .map(OrderProblemConfigVO::getProblemTypeList)
                        .flatMap(Collection::stream)
                        .map(OrderProblemConfigVO.ProblemType::getCode)
                        .collect(Collectors.toList());

                if(!searchCodes.isEmpty()){
                    if(codes.isEmpty()){
                        codes.addAll(searchCodes);
                    }else {
                        codes.retainAll(searchCodes);
                    }

                }
            }

            if(!codes.isEmpty()){
                map.put(RETURN_REASON_FIELD, codes);
                qry.setOtherConditions(new JSONObject(map));
            }
        }

        // 商品属性过滤
        if (RETURN_CATEGORY.equals(dimensionId)){
            if (StringUtil.isNotEmpty(prodCode)) {

                ProductCategoryListQry prodQry = new ProductCategoryListQry();

                List<String> stringIds = new ArrayList<>();
                if(StringUtil.isNotEmpty(prodCode)){
                    stringIds = Arrays.asList(prodCode.split(","));
                    List<Long> ids = stringIds.stream()
                            .map(Long::parseLong)
                            .collect(Collectors.toList());
                    prodQry.setIds(ids);
                }

                Response<List<ProductCategoryVO>> res = productCategoryRemoteService.selectList(prodQry);
                if (!res.isSuccess()) {
                    return Response.failure("RPC请求款式数据失败");
                }

                List<String> codes = res.get().stream()
                        .map(ProductCategoryVO::getCode).collect(Collectors.toList());

                map.put(CATEGORY_CODE_FIELD, codes);
            }
        }else if (RETURN_ASSORTMENT.equals(deptId)){
            if (StringUtil.isNotEmpty(prodCode)) {
                ProductAssortmentListQry prodQry = new ProductAssortmentListQry();
                List<String> stringIds = new ArrayList<>();
                if(StringUtil.isNotEmpty(prodCode)){
                    stringIds = Arrays.asList(prodCode.split(","));
                    List<Long> ids = stringIds.stream()
                            .map(Long::parseLong)
                            .collect(Collectors.toList());
                    prodQry.setIds(ids);
                }

                Response<List<ProductAssortmentVO>> res = productAssortmentRemoteService.selectList(prodQry);
                if (!res.isSuccess()) {
                    return Response.failure("RPC请求款式数据失败");
                }

                List<String> codes = res.get().stream()
                        .map(ProductAssortmentVO::getCode).collect(Collectors.toList());

                map.put(ASSORTMENT_CODE_FIELD, codes);
            }
        }else if (RETURN_STYLE.equals(dimensionId)){
            if (StringUtil.isNotEmpty(prodSeason) || StringUtil.isNotEmpty(isMainly) || StringUtil.isNotEmpty(prodLevel) || StringUtil.isNotEmpty(prodCode)) {

                ProductStylePageQry prodQry = new ProductStylePageQry();
                if(StringUtil.isNotEmpty(prodSeason)){
                    prodQry.setSeason(prodSeason);
                }

                if(StringUtil.isNotEmpty(isMainly)){
                    prodQry.setIsMainly(Integer.parseInt(isMainly));
                }

                if(StringUtil.isNotEmpty(prodLevel)){
                    prodQry.setLevel(prodLevel);
                }

                List<String> stringIds = new ArrayList<>();
                if(StringUtil.isNotEmpty(prodCode)){
                    stringIds = Arrays.asList(prodCode.split(","));
                    List<Long> ids = stringIds.stream()
                            .map(Long::parseLong)
                            .collect(Collectors.toList());
                    prodQry.setIds(ids);
                }

                Response<List<ProductStyleQueryVO>> res = productStyleRemoteService.selectList(prodQry);
                if (!res.isSuccess()) {
                    return Response.failure("RPC请求款式数据失败");
                }

                List<String> codes = res.get().stream()
                        .map(ProductStyleQueryVO::getCode).collect(Collectors.toList());

                map.put(STYLE_CODE_FIELD, codes);
            }
        }

        qry.setOtherConditions(map);

        return Response.of(qry);
    }
}
